﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace Maticsoft.DAL
{
	/// <summary>
	/// 数据访问类:Members
	/// </summary>
	public partial class Members
	{
        public DataSet GetMemberByUserNamePassword(string userName, string password)
        {
            string strSql = "select Members.UserId,Members.UserName,Members.GroupId,MemberGroup.GroupName from Members join MemberGroup on Members.GroupId=MemberGroup.GroupId where UserName=@UserName and Password=@Password and (Members.GroupId=1 or Members.GroupId=2) and Members.IsActive=1";
            SqlParameter[] para ={
                                new SqlParameter("@UserName",SqlDbType.NVarChar,50),
                                new SqlParameter("@Password",SqlDbType.NVarChar,50)
                                };
            para[0].Value = userName;
            para[1].Value = password;

            return DbHelperSQL.Query(strSql, para);
        }

        public Maticsoft.Model.Members GetModel(string userName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 UserId,UserName,Password,GroupId,Email,Mobile,Phone,Province,City,PostCode,Address,Question,Answer,QQ,MSN,IdentityCard,Credits,Pic,CreateTime,UpdateTime,LastLoginTime,IsActive from Members ");
            strSql.Append(" where UserName=@UserName");
            SqlParameter[] parameters = {
					new SqlParameter("@UserName", SqlDbType.NVarChar,50)
			};
            parameters[0].Value = userName;

            Maticsoft.Model.Members model = new Maticsoft.Model.Members();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["UserId"] != null && ds.Tables[0].Rows[0]["UserId"].ToString() != "")
                {
                    model.UserId = int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UserName"] != null && ds.Tables[0].Rows[0]["UserName"].ToString() != "")
                {
                    model.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Password"] != null && ds.Tables[0].Rows[0]["Password"].ToString() != "")
                {
                    model.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                }
                if (ds.Tables[0].Rows[0]["GroupId"] != null && ds.Tables[0].Rows[0]["GroupId"].ToString() != "")
                {
                    model.GroupId = int.Parse(ds.Tables[0].Rows[0]["GroupId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Email"] != null && ds.Tables[0].Rows[0]["Email"].ToString() != "")
                {
                    model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Mobile"] != null && ds.Tables[0].Rows[0]["Mobile"].ToString() != "")
                {
                    model.Mobile = ds.Tables[0].Rows[0]["Mobile"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Phone"] != null && ds.Tables[0].Rows[0]["Phone"].ToString() != "")
                {
                    model.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Province"] != null && ds.Tables[0].Rows[0]["Province"].ToString() != "")
                {
                    model.Province = int.Parse(ds.Tables[0].Rows[0]["Province"].ToString());
                }
                if (ds.Tables[0].Rows[0]["City"] != null && ds.Tables[0].Rows[0]["City"].ToString() != "")
                {
                    model.City = int.Parse(ds.Tables[0].Rows[0]["City"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PostCode"] != null && ds.Tables[0].Rows[0]["PostCode"].ToString() != "")
                {
                    model.PostCode = ds.Tables[0].Rows[0]["PostCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Address"] != null && ds.Tables[0].Rows[0]["Address"].ToString() != "")
                {
                    model.Address = ds.Tables[0].Rows[0]["Address"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Question"] != null && ds.Tables[0].Rows[0]["Question"].ToString() != "")
                {
                    model.Question = ds.Tables[0].Rows[0]["Question"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Answer"] != null && ds.Tables[0].Rows[0]["Answer"].ToString() != "")
                {
                    model.Answer = ds.Tables[0].Rows[0]["Answer"].ToString();
                }
                if (ds.Tables[0].Rows[0]["QQ"] != null && ds.Tables[0].Rows[0]["QQ"].ToString() != "")
                {
                    model.QQ = ds.Tables[0].Rows[0]["QQ"].ToString();
                }
                if (ds.Tables[0].Rows[0]["MSN"] != null && ds.Tables[0].Rows[0]["MSN"].ToString() != "")
                {
                    model.MSN = ds.Tables[0].Rows[0]["MSN"].ToString();
                }
                if (ds.Tables[0].Rows[0]["IdentityCard"] != null && ds.Tables[0].Rows[0]["IdentityCard"].ToString() != "")
                {
                    model.IdentityCard = ds.Tables[0].Rows[0]["IdentityCard"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Credits"] != null && ds.Tables[0].Rows[0]["Credits"].ToString() != "")
                {
                    model.Credits = int.Parse(ds.Tables[0].Rows[0]["Credits"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Pic"] != null && ds.Tables[0].Rows[0]["Pic"].ToString() != "")
                {
                    model.Pic = ds.Tables[0].Rows[0]["Pic"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CreateTime"] != null && ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UpdateTime"] != null && ds.Tables[0].Rows[0]["UpdateTime"].ToString() != "")
                {
                    model.UpdateTime = DateTime.Parse(ds.Tables[0].Rows[0]["UpdateTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LastLoginTime"] != null && ds.Tables[0].Rows[0]["LastLoginTime"].ToString() != "")
                {
                    model.LastLoginTime = DateTime.Parse(ds.Tables[0].Rows[0]["LastLoginTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsActive"] != null && ds.Tables[0].Rows[0]["IsActive"].ToString() != "")
                {
                    model.IsActive = int.Parse(ds.Tables[0].Rows[0]["IsActive"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        public Maticsoft.Model.Members GetModel(string userName,string password)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 UserId,UserName,Password,GroupId,Email,Mobile,Phone,Province,City,PostCode,Address,Question,Answer,QQ,MSN,IdentityCard,Credits,Pic,CreateTime,UpdateTime,LastLoginTime,IsActive from Members ");
            strSql.Append(" where UserName=@UserName and Password=@Password");
            SqlParameter[] parameters = {
					new SqlParameter("@UserName", SqlDbType.NVarChar,50),
                    new SqlParameter("@Password", SqlDbType.NVarChar,50)
			};
            parameters[0].Value = userName;
            parameters[1].Value = password;

            Maticsoft.Model.Members model = new Maticsoft.Model.Members();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["UserId"] != null && ds.Tables[0].Rows[0]["UserId"].ToString() != "")
                {
                    model.UserId = int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UserName"] != null && ds.Tables[0].Rows[0]["UserName"].ToString() != "")
                {
                    model.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Password"] != null && ds.Tables[0].Rows[0]["Password"].ToString() != "")
                {
                    model.Password = ds.Tables[0].Rows[0]["Password"].ToString();
                }
                if (ds.Tables[0].Rows[0]["GroupId"] != null && ds.Tables[0].Rows[0]["GroupId"].ToString() != "")
                {
                    model.GroupId = int.Parse(ds.Tables[0].Rows[0]["GroupId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Email"] != null && ds.Tables[0].Rows[0]["Email"].ToString() != "")
                {
                    model.Email = ds.Tables[0].Rows[0]["Email"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Mobile"] != null && ds.Tables[0].Rows[0]["Mobile"].ToString() != "")
                {
                    model.Mobile = ds.Tables[0].Rows[0]["Mobile"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Phone"] != null && ds.Tables[0].Rows[0]["Phone"].ToString() != "")
                {
                    model.Phone = ds.Tables[0].Rows[0]["Phone"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Province"] != null && ds.Tables[0].Rows[0]["Province"].ToString() != "")
                {
                    model.Province = int.Parse(ds.Tables[0].Rows[0]["Province"].ToString());
                }
                if (ds.Tables[0].Rows[0]["City"] != null && ds.Tables[0].Rows[0]["City"].ToString() != "")
                {
                    model.City = int.Parse(ds.Tables[0].Rows[0]["City"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PostCode"] != null && ds.Tables[0].Rows[0]["PostCode"].ToString() != "")
                {
                    model.PostCode = ds.Tables[0].Rows[0]["PostCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Address"] != null && ds.Tables[0].Rows[0]["Address"].ToString() != "")
                {
                    model.Address = ds.Tables[0].Rows[0]["Address"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Question"] != null && ds.Tables[0].Rows[0]["Question"].ToString() != "")
                {
                    model.Question = ds.Tables[0].Rows[0]["Question"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Answer"] != null && ds.Tables[0].Rows[0]["Answer"].ToString() != "")
                {
                    model.Answer = ds.Tables[0].Rows[0]["Answer"].ToString();
                }
                if (ds.Tables[0].Rows[0]["QQ"] != null && ds.Tables[0].Rows[0]["QQ"].ToString() != "")
                {
                    model.QQ = ds.Tables[0].Rows[0]["QQ"].ToString();
                }
                if (ds.Tables[0].Rows[0]["MSN"] != null && ds.Tables[0].Rows[0]["MSN"].ToString() != "")
                {
                    model.MSN = ds.Tables[0].Rows[0]["MSN"].ToString();
                }
                if (ds.Tables[0].Rows[0]["IdentityCard"] != null && ds.Tables[0].Rows[0]["IdentityCard"].ToString() != "")
                {
                    model.IdentityCard = ds.Tables[0].Rows[0]["IdentityCard"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Credits"] != null && ds.Tables[0].Rows[0]["Credits"].ToString() != "")
                {
                    model.Credits = int.Parse(ds.Tables[0].Rows[0]["Credits"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Pic"] != null && ds.Tables[0].Rows[0]["Pic"].ToString() != "")
                {
                    model.Pic = ds.Tables[0].Rows[0]["Pic"].ToString();
                }
                if (ds.Tables[0].Rows[0]["CreateTime"] != null && ds.Tables[0].Rows[0]["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(ds.Tables[0].Rows[0]["CreateTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["UpdateTime"] != null && ds.Tables[0].Rows[0]["UpdateTime"].ToString() != "")
                {
                    model.UpdateTime = DateTime.Parse(ds.Tables[0].Rows[0]["UpdateTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["LastLoginTime"] != null && ds.Tables[0].Rows[0]["LastLoginTime"].ToString() != "")
                {
                    model.LastLoginTime = DateTime.Parse(ds.Tables[0].Rows[0]["LastLoginTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsActive"] != null && ds.Tables[0].Rows[0]["IsActive"].ToString() != "")
                {
                    model.IsActive = int.Parse(ds.Tables[0].Rows[0]["IsActive"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        public int DeleteByIds(string ids)
        {
            string strSql = "Update  Members set IsActive=0 where UserId in(" + ids + ")";

            return DbHelperSQL.ExecuteSql(strSql);
        }
	}
}

